package com.feishi.project.javafx.excel.convert;

import com.feishi.core.util.common.FileUtil;
import com.feishi.core.util.common.StreamUtil;
import com.feishi.core.util.javafx.FxUtil;
import com.feishi.project.javafx.excel.convert.util.ExcelUtil;
import com.feishi.project.javafx.excel.convert.util.YamlUtil;
import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.scene.input.MouseEvent;
import javafx.stage.FileChooser;
import javafx.stage.Stage;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tools.ant.util.StreamUtils;

import java.io.*;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: caixq
 * Date: 2020-10-31
 * Time: 下午1:48
 */
public class ConvertController {
    private  Map config;
    private String targetSheet;


    @FXML
    private Button analysisFile;
    @FXML
    private Button selectFile;
    private File file;
    private String outPath;

    @FXML
    private Label inFileShow;
    @FXML
    private Label outFileShow;
    @FXML
    private TextField rule;



    public void selectFile(MouseEvent mouseEvent) {
        this.file = FxUtil.openFile(selectFile);
        if(file!=null){

            System.out.println(file.getPath());
            inFileShow.setText("源文件:"+file.getPath());
            this.outPath = file.getPath()+".zip";
        }
    }

    public ConvertController() {
        this.config=YamlUtil.load("map.yml");
        this.targetSheet=YamlUtil.getConfigValue(config,"target","Sheet1",String.class);
    }

    public void analysisFile(MouseEvent mouseEvent) throws IOException {
        if (file==null)return;
        Workbook wb = null;
        String[] split = file.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
        //根据文件后缀（xls/xlsx）进行判断
        for (String s : Charset.availableCharsets().keySet()) {
            System.out.println(s);
        }
        if ( "xls".equals(split[split.length-1])){
            wb = new HSSFWorkbook(new FileInputStream(file));
        }else if ("xlsx".equals(split[split.length-1])){
            wb = new XSSFWorkbook(new FileInputStream(file));
        }else {
            System.out.println("文件类型错误!");
            return;
        }
        Sheet sheet=null;
        for (Iterator<Sheet> it = wb.sheetIterator(); it.hasNext(); ) {
            Sheet s = it.next();
            System.out.println(s.getSheetName());
            if(targetSheet.equals(s.getSheetName())){
                sheet=s;
            }
        }
        if(sheet==null)return;





        int readStatus=0;
        List<Map<String,Object>> result=new ArrayList<>();
        Map dict=YamlUtil.getConfigValue(config,"title.content",null,Map.class);
        Map<Integer,String> titleIndex=new HashMap();
        for (Row row : sheet) {
            if (row==null) {
                continue;
            }
//            System.out.print("row:"+row.getRowNum()+"\t");
            Map<String,Object> data=new HashMap<>();
            for (Cell cell : row) {
                if (cell==null) {
                    continue;
                }
                String content=parseCell(cell).split("\n")[0]/*getStringCell(cell).split("\n")[0]*/;
                if(readStatus==2){
                    content=parseCell(cell);
                }
                if(readStatus==0&&("".equals(content)||!"项目".equals(content)))continue;
                if("项目".equals(content)){
                    readStatus=1;
                }

                if("1".equals(content)){
                    readStatus=2;
                }

                if(readStatus==1){
                    if(dict.get(content)!=null){
                        titleIndex.put(cell.getColumnIndex(),content);
                    }else {
//                        System.out.println(111);
                    }
                }
                if(readStatus==2){
//                    System.out.println("读数");
                    data.put((String) dict.get(titleIndex.get(cell.getAddress().getColumn())),content);
                    if("A3".equals( dict.get(titleIndex.get(cell.getAddress().getColumn())))){
//                        System.out.println(111);
                    }
                }

                //System.out.print("|"+getStringCell(cell).replace("\n"," ")+"\t|");
            }
            if(readStatus==2){
                result.add(data);
            }

           // System.out.println();

        }
        Map<String,byte[]> out=new HashMap<>();
        String ru = rule.getText();
        String[] rs = ru.split(" ");

        noticeOutFileShow("正在转换");


        for (Map<String, Object> stringObjectMap : result) {
            if(emptyNum(stringObjectMap)<10){
                if(contain(Integer.parseInt((String) stringObjectMap.get("AA")),rs)){
                    System.out.println("正在转换第"+(String) stringObjectMap.get("AA")+"");
                    outFileShow.setText("正在转换第"+(String) stringObjectMap.get("AA")+"");
                    startCreateExcel(stringObjectMap,out);
                }

            }
        }
        FileUtil.writeStreamToFile(new ByteArrayInputStream(StreamUtil.createZipFile(out)),new File(outPath));
        //因为Sheet接口继承了 java.lang.Iterable接口所以，遍历表中的行可以一用foreach很方便　　　　
        outFileShow.setText("解析完成，文件已生成:"+outPath);
    }

    private void noticeOutFileShow(String msg) {
        outFileShow.setText(msg);
    }

    private boolean contain(int aa, String[] rs) {
        for (String r : rs) {
            String[] rr = r.split(":");
            if(rr.length==1){
                if(rr[0].equals(aa))return true;
            }else if(rr.length==2){
                int out=0;
                if("".equals(rr[0])){
                    out++;
                }else if(Integer.parseInt(rr[0])<=aa){
                    out++;
                }
                if("".equals(rr[1])){
                    out++;
                }else if(Integer.parseInt(rr[1])>=aa){
                    out++;
                }
                if(out==2)return true;
            }

        }
        return true;
    }

    private int emptyNum(Map<String, Object> stringObjectMap) {
        int result=0;
        for (Object s : stringObjectMap.values()) {
            if("".equals(s)||s==null){
                result++;
            }
        }
        return result;
    }



    private void startCreateExcel(Map data, Map<String, byte[]> out) {
        String fileName="SIL+"+
                ("".equals(data.get("A1"))?("TMP"+data.get("AA")+"_"+(int)(Math.random()*100000)):data.get("A1"))
                +"+def+proforma_"+data.get("A2").toString().replace("/","-")+".xls";


        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("template.xlsx")) {
            try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
                ExcelUtil.export(is,os,data);
                out.put(fileName,os.toByteArray());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public String parseCell(Cell cell) {
        if (cell == null)
            return "";
        if (cell.getCellType() == STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == NUMERIC) {
            short format = cell.getCellStyle().getDataFormat();
            SimpleDateFormat sdf = null;
            if (format == 14 || format == 31 || format == 57 || format == 58
                    || (176==format || format==178) || (182<=format && format<=196)
                    || (210<=format && format<=213) || (208==format ) ) { // 日期
                sdf = new SimpleDateFormat("yyyy/MM/dd");
            } else if (format == 20 ||format == 177 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
                sdf = new SimpleDateFormat("HH:mm:ss");
            } else { // 不是日期格式
                cell.setCellType(STRING);
                return cell.getRichStringCellValue().toString().trim();
                //return String.valueOf(cell.getNumericCellValue());这样会导致excel单元格设置为文本，但是存储数字的读取到的字符串为2.123E5之类的指数类型，所以笔者选择上面的处理方式将非日期的单元格先强制设置为文本格式，然后按照文本格式读取！
            }
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            if(date==null || "".equals(date)){
                return "";
            }
            String result="";
            try {
                result = sdf.format(date);
            } catch (Exception e) {
                e.printStackTrace();
                return "";
            }
            return result;
        }
        return "";
    }



    private Object getCellValue(Cell cell) {
        Object result = null;
        CellType cellType = cell.getCellTypeEnum();
//        System.out.println(cellType);
        switch (cellType) {
            case BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                result = cell.getNumericCellValue();
                break;
            case STRING:
                result = cell.getStringCellValue();
                break;
            case FORMULA:
                result = cell.getDateCellValue();
                break;
            case ERROR:
                result = cell.getErrorCellValue();
                break;
            default:
                break;
        }
        return result;
    }

    public static String getStringCell(Cell cell) {
        String result = "";
        CellType cellType = cell.getCellTypeEnum();
//        System.out.println(cellType);
        switch (cellType) {
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                result = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                result = cell.getStringCellValue();
                break;
            default:
                break;
        }


        return result;
    }
}
